﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;

namespace DBTools
{
    internal static class DB
    {
        private static SqlConnection conn;

        public static string DataBase { get => conn.Database; }

        public static void Init(string host, string user, string passwd, string port = "3306", string database = "master")
        => Init(new SqlConnectionStringBuilder()
        {
            DataSource = host,
            UserID = user,
            Password = passwd,
            IntegratedSecurity = false,
            InitialCatalog = database,
            Pooling = true,
        }.ConnectionString);

        public static void Init(string connectionString)
            => (conn = new SqlConnection(connectionString)).Open();

        public static void SetDataBase(string database) => conn.ChangeDatabase(database);

        public static DataTable ExecuteToDataTable(FormattableString sql)
        {
            using (SqlDataAdapter adapt = new SqlDataAdapter(GetCommand(sql)))
            {
                DataTable dt = new DataTable();
                adapt.Fill(dt);
                return dt;
            }
        }

        public static object ExecuteScalar(FormattableString sql)
        {
            using (var cmd = GetCommand(sql))
            {
                return cmd.ExecuteScalar();
            }
        }

        public static List<string> ExecuteToList(FormattableString sql)
        {
            using (SqlDataReader reader = GetCommand(sql).ExecuteReader())
            {
                List<string> res = new List<string>();
                while (reader.Read())
                {
                    res.Add(reader.GetString(0));
                }

                return res;
            }
        }

        private static SqlCommand GetCommand(FormattableString sql)
        {
            var (Sql, Parameters) = BuildSqlAndParameters(sql);
            var cmd = new SqlCommand(Sql, conn);
            cmd.Parameters.AddRange(Parameters);
            return cmd;
        }

        static (string Sql, SqlParameter[] Parameters) BuildSqlAndParameters(FormattableString sql)
        {
            List<string> formatArgs = new List<string>(sql.ArgumentCount);
            List<SqlParameter> parameters = new List<SqlParameter>(sql.ArgumentCount);

            string parameterPrefix = "@P_";

            foreach (var arg in sql.GetArguments())
            {
                object paramValue = arg;
                if (paramValue == null || paramValue == DBNull.Value)
                {
                    formatArgs.Add("NULL");
                    continue;
                }

                Type paramType = arg.GetType();

                if (paramType.IsEnum)
                {
                    paramType = Enum.GetUnderlyingType(paramType);
                    if (paramValue != null)
                        paramValue = Convert.ChangeType(paramValue, paramType);
                }

                SqlParameter p = parameters.Where(a => object.Equals(a.Value, paramValue)).FirstOrDefault();

                if (p != null)
                {
                    formatArgs.Add(p.ParameterName);
                    continue;
                }

                string paramName = parameterPrefix + parameters.Count.ToString();
                p = new SqlParameter(paramName, paramValue);
                parameters.Add(p);
                formatArgs.Add(p.ParameterName);
            }

            string runSql = string.Format(sql.Format, formatArgs.ToArray());
            return (runSql, parameters.ToArray());
        }
    }
}
